﻿package com.huasoft.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.huasoft.domain.User;
import com.huasoft.util.JDBConnection;

public class UserDao {
	/**
	 * �û���¼ʱ����˺ţ������ж��Ƿ���ݿ��д��用户登录时根据账号，密码判断是否数据库中存在此用户��û�
	 */
	public User load(String loginName, String password) {
		String sql = "select * from users where loginname=? and password=?";
		Connection con = null;
		PreparedStatement ps = null;
		User user = null;
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, loginName);
			ps.setString(2, password);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = new User();
				user.setId(rs.getInt(1));
				user.setLoginname(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setName(rs.getString(4));
				user.setAge(rs.getInt(5));
				user.setGender(rs.getString(6));
				user.setBirthday(rs.getString(7));
				user.setDeptid(rs.getInt(8));
				user.setRoleid(rs.getInt(9));
			}
			JDBConnection.closeConnection(con, ps, rs);
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println(user);
		return user;
	}

	/**
	 *根据管理员所填的数据增加一个用户���û�
	 */
	public boolean save(String loginname, String password, String username,
			Integer age, String gender, String birthday, Integer deptid,
			Integer roleid) {
		String sql = "insert into users values(null,?,?,?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, loginname);
			ps.setString(2, password);
			ps.setString(3, username);
			ps.setInt(4, age);
			ps.setString(5, gender);
			ps.setString(6, birthday);
			ps.setInt(7, deptid);
			ps.setInt(8, roleid);
			int rowNumber = ps.executeUpdate();
			if (rowNumber > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				JDBConnection.closeConnection(con, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
	}

	/**
	 * 根据查询条件查找符合的用户�ϵ��û�
	 * 
	 * @param name
	 * @param deptid
	 * @param roleid
	 * @param pageCondition
	 * @return
	 */
	public List<User> listUser(String name, Integer deptid, Integer roleid,
			PageCondition pageCondition) {
		StringBuffer sql = new StringBuffer("select * from users where 1=1");
		Connection con = null;
		Statement st = null;
		List<User> users = new ArrayList<User>();
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			if (name != null) {
				sql.append(" and name like '%" + name + "%'");
			}
			if (deptid != null && deptid > 0) {
				sql.append(" and dept_id=" + deptid);
			}
			if (roleid != null && roleid > 0) {
				sql.append(" and role_id=" + roleid);
			}
			System.out.println(sql.toString());
			int begin = (pageCondition.getPage() - 1)
					* pageCondition.getPageSize();
			System.out.println("begin" + begin);
			int end = pageCondition.getPageSize();
			System.out.println("end" + end);
			sql.append(" limit " + begin + "," + end);
			System.out.println(sql);
			//返回操作数据库的对象
			st = con.createStatement();
			rs = st.executeQuery(sql.toString());
			while (rs.next()) {
				User user = new User();
				user.setAge(rs.getInt("age"));
				user.setLoginname(rs.getString("loginname"));
				user.setBirthday(rs.getString("birthday"));
				user.setDeptid(rs.getInt("dept_id"));
				user.setRoleid(rs.getInt("role_id"));
				user.setGender(rs.getString("gender"));
				user.setName(rs.getString("name"));
				user.setId(rs.getInt("id"));
				user.setPassword(rs.getString("password"));
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, st, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
		}
		System.out.println(users.size() + "abx");
		return users;
	}

	/**
	 *总的页数��
	 * 
	 * @param name
	 * @param deptid
	 * @param roleid
	 * @param pageCondition
	 */
	public void totalPage(String name, Integer deptid, Integer roleid,
			PageCondition pageCondition) {
		StringBuffer sql = new StringBuffer(
				"select count(*) from users where 1=1");
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			if (name != null) {
				sql.append(" and name like '%" + name + "%'");
			}
			if (deptid != null && deptid > 0) {
				sql.append(" and dept_id=" + deptid);
			}
			if (roleid != null && roleid > 0) {
				sql.append(" and role_id=" + roleid);
			}
			st = con.createStatement();
			rs = st.executeQuery(sql.toString());
			rs.next();
			int num = rs.getInt(1);
			if (num % pageCondition.getPageSize() == 0) {
				pageCondition.setTotalPage(num / pageCondition.getPageSize());
			} else {
				pageCondition.setTotalPage(num / pageCondition.getPageSize()
						+ 1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, st, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 根据用户主键查找相应的用户���û�
	 * 
	 * @param id
	 * @return
	 */
	public User findById(Integer id) {
		String sql = "select * from users where id=?";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		User user = null;
		try {
			con = JDBConnection.getConnection();
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = new User();
				user.setAge(rs.getInt("age"));
				user.setLoginname(rs.getString("loginname"));
				user.setBirthday(rs.getString("birthday"));
				System.out.println(user.getBirthday());
				user.setDeptid(rs.getInt("dept_id"));
				user.setRoleid(rs.getInt("role_id"));
				user.setGender(rs.getString("gender"));
				user.setName(rs.getString("name"));
				user.setId(rs.getInt("id"));
				user.setPassword(rs.getString("password"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
		}
		return user;
	}

	/**
	 * 根据用户主键更新用户的信息
	 * 
	 * @param loginname
	 * @param password
	 * @param username
	 * @param age
	 * @param gender
	 * @param birth
	 * @param deptid
	 * @param roleid
	 * @return
	 */
	public boolean update(User user) {
		String sql = "update users set loginname=? , password=? "
				+ ", name=? , age=? , gender=?"
				+ " , birthday=? , dept_id=? , role_id=? where id=?";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, user.getLoginname());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getName());
			ps.setInt(4, user.getAge());
			ps.setString(5, user.getGender());
			ps.setString(6, user.getBirthday());
			ps.setInt(7, user.getDeptid());
			ps.setInt(8, user.getRoleid());
			ps.setInt(9, user.getId());
			int rowNumber = ps.executeUpdate();
			System.out.println(rowNumber);
			if (rowNumber > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				JDBConnection.closeConnection(con, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
	}

	public boolean deleteById(Integer id) {
		String sql = "delete from users where id=?";
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = JDBConnection.getConnection();
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			int rowNum = ps.executeUpdate();
			if (rowNum > 0) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, ps, null);
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return false;
	}

	public User findManagerById(Integer id) {
		String sql = "select a.id,a.loginname,a.password,a.name,"
				+ "a.age,a.gender,a.birthday,a.dept_id,a.role_id"
				+ " from users a,users b,role r where b.id=?"
				+ " and a.dept_id=b.dept_id and a.role_id=r.id and r.name='部门经理'";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		User user = null;
		try {
			con = JDBConnection.getConnection();
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = new User();
				user.setId(rs.getInt(1));
				user.setAge(rs.getInt(5));
				user.setLoginname(rs.getString(2));
				user.setBirthday(rs.getString(7));
				System.out.println(user.getBirthday());
				user.setDeptid(rs.getInt(8));
				user.setRoleid(rs.getInt(9));
				user.setGender(rs.getString(6));
				user.setName(rs.getString(4));
				user.setPassword(rs.getString(3));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
		}
		return user;
	}
}
